Imports System.IO

Public Class frmUpload

    Public Sub WriteToStatusScreen(ByVal Clear As Boolean, ByVal msg As String)
        If Clear Then
            txtStatusMsg.Text = ""
        End If
        txtStatusMsg.HideSelection = True
        txtStatusMsg.Text &= msg & vbCrLf
        txtStatusMsg.SelectAll()
        txtStatusMsg.ScrollToCaret()
        txtStatusMsg.Refresh()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If Me.txtFileName.Text = String.Empty Then
            MsgBox("Please Select File", MsgBoxStyle.Information, "Non-FHN3 Batch Upload")
            Exit Sub
        End If
        System.Windows.Forms.Cursor.Current = Cursors.WaitCursor
        WriteToStatusScreen(True, "Please wait File Upload in progress ...")
        Me.Button1.Enabled = Not Me.Button1.Enabled

        Call btnUpload_Click(Me, New System.EventArgs)

        Me.Button1.Enabled = Not Me.Button1.Enabled

        System.Windows.Forms.Cursor.Current = Cursors.Default

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.txtStatusMsg.Clear()

    End Sub

    Private Sub Button3_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Me.Close()
    End Sub


    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

        OpenFD.InitialDirectory = "C:\"
        OpenFD.Filter = "Excel Files(*.xls)|*.xls|Excel Files(*.xlsx)|*.xlsx|All Files(*.*)|*.*"
        OpenFD.ShowDialog()
        Me.txtFileName.Text = OpenFD.FileName

    End Sub

    Private Sub UploadFile()

        Dim sFuncName As String = "UploadFile()"
        Dim sErrdesc As String = String.Empty

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            WriteToStatusScreen(False, "========================== S T A R T =======================================")

            If Me.DocType.Text = "A/R Invoice" Then
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling UploadDocument()", sFuncName)
                If UploadDocument_ARInvoice(Me.txtFileName.Text, sErrdesc) <> RTN_SUCCESS Then
                    WriteToStatusScreen(False, "========================== COMPLETED WITH ERROR =======================================")
                    'error condition.
                End If
            End If

            If Me.DocType.Text = "A/P Invoice" Then
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling UploadDocument()", sFuncName)
                If UploadDocument_APInvoice(Me.txtFileName.Text, sErrdesc) <> RTN_SUCCESS Then
                    WriteToStatusScreen(False, sErrdesc)
                    WriteToStatusScreen(False, "========================== COMPLETED WITH ERROR =======================================")
                    'error condition
                End If
            End If

            WriteToStatusScreen(False, "========================== COMPLETED =======================================")

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function Completed successfully.", sFuncName)
        Catch ex As Exception
            sErrdesc = ex.Message
            Call WriteToLogFile(sErrdesc, sFuncName)
            Throw New ArgumentException(sErrdesc)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Completed function with ERROR", sFuncName)
        End Try
    End Sub

    Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim sErrdesc As String = String.Empty
        If GetSystemIntializeInfo(p_oCompDef, sErrdesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrdesc)
        UploadFile()
    End Sub


    Public Function UploadDocument_APInvoice(ByVal sFileName As String, ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "UploadDocument_APInvoice()"
        Dim myfile As New System.IO.FileInfo(sFileName)
        Dim sSheet1 As String = String.Empty
        Dim sSheet2 As String = String.Empty
        Dim sFileType As String = String.Empty
        Dim bIsError As Boolean = False
        Dim odv As DataView = Nothing
        Dim iCnt As Integer = 0

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            WriteToStatusScreen(False, "Validating Excel file format.....")

            bIsError = False
            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling ReadAPFile", sFuncName)
            ReadAPFile(sFileName, "Sheet1", bIsError, odv, sErrDesc)

            If bIsError = True Then
                WriteToStatusScreen(False, " ERROR :::  " & sErrDesc)
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Invalid Billl To Client Excel Worksheet", sFuncName)
                WriteToLogFile("Invalid Excel Worksheet " & sFileName, sFuncName)
                MsgBox("Invalid Excel Worksheet", MsgBoxStyle.Critical, "NON-FHN3 Upload")
                GoTo ExitFunc
            End If

            WriteToStatusScreen(False, "Successfully Validated Excel file format.")

            WriteToStatusScreen(False, "Connecting to SAP Database...")

            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling ConnectToCompany()", sFuncName)
            If ConnectToCompany(p_oCompany, sErrDesc) <> RTN_SUCCESS Then
                WriteToStatusScreen(False, "ERROR:: Unable to connect to SAP." & sErrDesc)
                Exit Function
            End If

            WriteToStatusScreen(False, "Successfully connected to SAP Database...")

            If p_oCompany.Connected Then
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction..", sFuncName)
                If StartTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)

                Dim oDT As DataTable
                oDT = odv.Table.DefaultView.ToTable(True, "F1", "F6")
                oDT.Rows(0).Delete()

                For Each row As DataRow In oDT.Rows
                    Dim oDtRows() As DataRow = odv.Table.Select("F1='" & row.Item(0).ToString & "' and F6='" & row.Item(1).ToString & "'")
                    If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling AddAPInvoice()", sFuncName)
                    WriteToStatusScreen(False, "Creating AP Invoice .....")
                    If AddAPInvoice(oDtRows, sErrDesc) <> RTN_SUCCESS Then
                        Throw New ArgumentException(sErrDesc)
                    End If
                Next

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling CommitTransaction()", sFuncName)
                If CommitTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Moving " & Me.txtFileName.Text & " to " & p_oCompDef.sSuccessDir, sFuncName)
                Dim UploadedFileName As String = Mid(Me.txtFileName.Text, 1, Me.txtFileName.Text.Length - 4) & "_" & Now.ToString("yyyyMMddhhmmss") & ".txt"
                WriteToStatusScreen(False, "Moving file to Success Folder")

                Dim k As Integer = Microsoft.VisualBasic.InStrRev(UploadedFileName, "\")
                Dim sFName As String = Microsoft.VisualBasic.Right(UploadedFileName, Len(UploadedFileName) - k).Trim

                System.IO.File.Move(Me.txtFileName.Text, p_oCompDef.sSuccessDir & "\" & Replace(sFName, ".txt", ".xlsx"))

                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Disconnecting from SAP Databases", sFuncName)
                p_oCompany.Disconnect()

                WriteToStatusScreen(False, "Disconnected from SAP Database.")

                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Disconnected from SAP Databases", sFuncName)

            Else
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Unable to connect to SAP.", sFuncName)
                Throw New ArgumentException("Unable to connect to SAP.")
            End If
ExitFunc:
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed with SUCCESS", sFuncName)
            UploadDocument_APInvoice = RTN_SUCCESS

        Catch ex As Exception
            UploadDocument_APInvoice = RTN_ERROR
            If RollBackTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)
            WriteToLogFile(ex.Message, sFuncName)
            sErrDesc = ex.Message
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed with ERROR", sFuncName)
        Finally

        End Try
    End Function

    Public Function UploadDocument_ARInvoice(ByVal sFileName As String, ByRef sErrDesc As String) As Long

        Dim sFuncName As String = "UploadDocument_ARInvoice()"
        Dim myfile As New System.IO.FileInfo(sFileName)
        Dim sSheet1 As String = String.Empty
        Dim sSheet2 As String = String.Empty
        Dim sFileType As String = String.Empty
        Dim bIsError As Boolean = False
        Dim odv As DataView = Nothing
        Dim iCnt As Integer = 0

        Try
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting Function", sFuncName)

            WriteToStatusScreen(False, "Validating Excel file format.....")

            bIsError = False
            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling ReadARFile", sFuncName)
            ReadARFile(sFileName, "Sheet1", bIsError, odv, sErrDesc)

            If bIsError = True Then
                WriteToStatusScreen(False, " ERROR :::  " & sErrDesc)
                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Invalid Billl To Client Excel Worksheet", sFuncName)
                WriteToLogFile("Invalid Excel Worksheet " & sFileName, sFuncName)
                MsgBox("Invalid Excel Worksheet", MsgBoxStyle.Critical, "NON-FHN3 Upload")
                GoTo ExitFunc
            End If

            WriteToStatusScreen(False, "Successfully Validated Excel file format.")

            WriteToStatusScreen(False, "Connecting to SAP Database...")

            If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Calling ConnectToCompany()", sFuncName)
            If ConnectToCompany(p_oCompany, sErrDesc) <> RTN_SUCCESS Then
                WriteToStatusScreen(False, "ERROR:: Unable to connect to SAP." & sErrDesc)
                Exit Function
            End If

            WriteToStatusScreen(False, "Successfully connected to SAP Database...")

            If p_oCompany.Connected Then
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Starting transaction..", sFuncName)
                If StartTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)

                For Each row As DataRow In odv.Table.Rows
                    iCnt += 1
                    If iCnt >= 6 Then

                        If row.Item(1).ToString = String.Empty Then Exit For

                        WriteToStatusScreen(False, "Creating Invoice for Line ::" & iCnt - 5)
                        If AddARInvoice(odv.Table, row, sErrDesc) <> RTN_SUCCESS Then
                            WriteToStatusScreen(False, "ERROR :: Rollback Transaction..")
                            WriteToStatusScreen(False, "ERROR :: Failed to create Invoice for Line ::" & iCnt - 5)
                            WriteToStatusScreen(False, "ERROR :: " & sErrDesc)
                            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("File was not successfully uploaded" & Me.txtFileName.Text, sFuncName)
                            If RollBackTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)
                            Throw New ArgumentException(sErrDesc)
                        End If
                        WriteToStatusScreen(False, "Successfully created Invoice for Line ::" & iCnt - 5)
                    End If
                Next

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Calling CommitTransaction()", sFuncName)
                If CommitTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)

                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Moving " & Me.txtFileName.Text & " to " & p_oCompDef.sSuccessDir, sFuncName)
                Dim UploadedFileName As String = Mid(Me.txtFileName.Text, 1, Me.txtFileName.Text.Length - 4) & "_" & Now.ToString("yyyyMMddhhmmss") & ".txt"
                WriteToStatusScreen(False, "Moving file to Success Folder")

                Dim k As Integer = Microsoft.VisualBasic.InStrRev(UploadedFileName, "\")
                Dim sFName As String = Microsoft.VisualBasic.Right(UploadedFileName, Len(UploadedFileName) - k).Trim

                System.IO.File.Move(Me.txtFileName.Text, p_oCompDef.sSuccessDir & "\" & Replace(sFName, ".txt", ".xlsx"))

                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Disconnecting from SAP Databases", sFuncName)
                p_oCompany.Disconnect()

                WriteToStatusScreen(False, "Disconnected from SAP Database.")

                If p_iDebugMode = DEBUG_ON Then WriteToLogFile_Debug("Disconnected from SAP Databases", sFuncName)
            Else
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Unable to connect to SAP.", sFuncName)
                Throw New ArgumentException("Unable to connect to SAP.")
            End If
ExitFunc:
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed with SUCCESS", sFuncName)
            UploadDocument_ARInvoice = RTN_SUCCESS

        Catch ex As Exception
            UploadDocument_ARInvoice = RTN_ERROR
            If RollBackTransaction(sErrDesc) <> RTN_SUCCESS Then Throw New ArgumentException(sErrDesc)
            WriteToLogFile(ex.Message, sFuncName)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Function completed with ERROR", sFuncName)
        Finally

        End Try
    End Function


    Private Sub ReadARFile(ByVal sFileName As String, _
                                ByVal sSheet As String, _
                                ByRef bIsError As Boolean, _
                                ByRef dv As DataView, _
                                ByRef sErrdesc As String)

        Dim iHeaderRow As Integer
        Dim sCardName As String = String.Empty
        Dim sFuncName As String = "ReadARFile"
        Dim sBatchNo As String = String.Empty

        iHeaderRow = 4

        dv = GetDataViewFromExcel(sFileName, sSheet)


        If IsNothing(dv) Then
            Exit Sub
        End If

        If dv(iHeaderRow)(0).ToString.Trim <> "No." Then
            sErrdesc = "Invalid Excel file Format - ([No.] not found at Column 1"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(1).ToString.Trim <> "Member Name" Then
            sErrdesc = "Invalid Excel file Format - ([Member Name] not found at Column 2"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(2).ToString.Trim <> "Member ID" Then
            sErrdesc = "Invalid Excel file Format - ([Member ID] not found at Column 3"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(3).ToString.Trim <> "Provider" Then
            sErrdesc = "Invalid Excel file Format - ([Provider] not found at Column 4"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(4).ToString.Trim <> "Provider Address" Then
            sErrdesc = "Invalid Excel file Format - ([Provider Address] not found at Column 5"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(5).ToString.Trim <> "Visit Date" Then
            sErrdesc = "Invalid Excel file Format - ([Visit Date] not found at Column 6"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(6).ToString.Trim <> "Bill Num" Then
            sErrdesc = "Invalid Excel file Format - ([Bill Num] not found at Column 7"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(7).ToString.Trim <> "Bill Received" Then
            sErrdesc = "Invalid Excel file Format - ([Bill Received] not found at Column 8"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(8).ToString.Trim <> "Surgeon Fee / Specialist Fee" Then
            sErrdesc = "Invalid Excel file Format - ([Surgeon Fee / Specialist Fee] not found at Column 9"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(9).ToString.Trim <> "Anaesthetist" Then
            sErrdesc = "Invalid Excel file Format - ([Anaesthetist] not found at Column 10"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(10).ToString.Trim <> "Consultation Fee" Then
            sErrdesc = "Invalid Excel file Format - ([Consultation Fee] not found at Column 11"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(11).ToString.Trim <> "Drugs Cost" Then
            sErrdesc = "Invalid Excel file Format - ([Drugs Cost] not found at Column 12"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(12).ToString.Trim <> "Inpatient Services" Then
            sErrdesc = "Invalid Excel file Format - ([Inpatient Services] not found at Column 13"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(13).ToString.Trim <> "Discount" Then
            sErrdesc = "Invalid Excel file Format - ([Discount] not found at Column 14"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(14).ToString.Trim <> "Deductible / Co-insurance" Then
            sErrdesc = "Invalid Excel file Format - ([Deductible / Co-insurance] not found at Column 15"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(15).ToString.Trim <> "Non payable Amount" Then
            sErrdesc = "Invalid Excel file Format - ([Non payable Amount] not found at Column 16"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(16).ToString.Trim <> "Currency" Then
            sErrdesc = "Invalid Excel file Format - ([Currency] not found at Column 17"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(17).ToString.Trim <> "Total Fees Bills (MYR)" Then
            sErrdesc = "Invalid Excel file Format - ([Total Fees Bills (MYR)] not found at Column 18"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(18).ToString.Trim <> "Approved Fees" Then
            sErrdesc = "Invalid Excel file Format - ([Approved Fees] not found at Column 19"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(19).ToString.Trim <> "Conversion Date" Then
            sErrdesc = "Invalid Excel file Format - ([Conversion Date] not found at Column 20"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(20).ToString.Trim <> "Amount (SGD)" Then
            sErrdesc = "Invalid Excel file Format - ([Amount (SGD)] not found at Column 21"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(21).ToString.Trim <> "Exchange Rate" Then
            sErrdesc = "Invalid Excel file Format - (Exchange Rate] not found at Column 21"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(22).ToString.Trim <> "Tax Invoice Num" Then
            sErrdesc = "Invalid Excel file Format - ([Tax Invoice Num] not found at Column 22"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(23).ToString.Trim <> "Claim ID" Then
            sErrdesc = "Invalid Excel file Format - ([Claim ID] not found at Column 23"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

    End Sub

    Private Sub ReadAPFile(ByVal sFileName As String, _
                                ByVal sSheet As String, _
                                ByRef bIsError As Boolean, _
                                ByRef dv As DataView, _
                                ByRef sErrdesc As String)

        Dim iHeaderRow As Integer
        Dim sCardName As String = String.Empty
        Dim sFuncName As String = "ReadAPFile"
        Dim sBatchNo As String = String.Empty

        iHeaderRow = 0

        dv = GetDataViewFromExcel(sFileName, sSheet)


        If IsNothing(dv) Then
            Exit Sub
        End If

        If dv(iHeaderRow)(0).ToString.Trim <> "Vendor Code" Then
            sErrdesc = "Invalid Excel file Format - [Vendor Code] not found at Column 1"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(1).ToString.Trim <> "Vendor Name" Then
            sErrdesc = "Invalid Excel file Format - [Vendor Name] not found at Column 2"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(2).ToString.Trim <> "Posting Date" Then
            sErrdesc = "Invalid Excel file Format - [Posting Date] not found at Column 3"
            WriteToStatusScreen(False, sErrdesc)
            WriteToLogFile(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(3).ToString.Trim <> "Due Date" Then
            sErrdesc = "Invalid Excel file Format - ([Due Date] not found at Column 4"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(4).ToString.Trim <> "Document Date" Then
            sErrdesc = "Invalid Excel file Format - ([Document Date] not found at Column 5"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(5).ToString.Trim <> "Vendor Ref No" Then
            sErrdesc = "Invalid Excel file Format - ([Vendor Ref No] not found at Column 6"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(6).ToString.Trim <> "LineNumber" Then
            sErrdesc = "Invalid Excel file Format - ([LineNumber] not found at Column 7"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(7).ToString.Trim <> "Description" Then
            sErrdesc = "Invalid Excel file Format - [Description] not found at Column 8"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(8).ToString.Trim <> "GL" Then
            sErrdesc = "Invalid Excel file Format - [GL] not found at Column 9"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(9).ToString.Trim <> "Distibution Rule" Then
            sErrdesc = "Invalid Excel file Format - [Distibution Rule] not found at Column 10"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(10).ToString.Trim <> "Tax Code" Then
            sErrdesc = "Invalid Excel file Format - [Tax Code] not found at Column 11"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(11).ToString.Trim <> "BP Currency" Then
            sErrdesc = "Invalid Excel file Format - [BP Currency] not found at Column 12"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

        If dv(iHeaderRow)(12).ToString.Trim <> "Total (LC)" Then
            sErrdesc = "Invalid Excel file Format - [Total (LC)] not found at Column 13"
            WriteToLogFile(False, sErrdesc)
            WriteToStatusScreen(False, sErrdesc)
            bIsError = True
        End If

    End Sub

    Private Function AddARInvoice(ByVal oDt As DataTable, _
                                      ByVal Row As DataRow, _
                                      ByRef sErrDesc As String) As Long

        Dim sFuncName As String = String.Empty
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim lRetCode, lErrCode As Long
        Dim sRemarks As String = String.Empty
        Dim sCostCenter As String = String.Empty
        Dim bLine As Boolean = False
        Try
            sFuncName = "AddARInvoice_FHG"


            Dim k As Integer = Microsoft.VisualBasic.InStrRev(Me.txtFileName.Text, "\")
            Dim sFName As String = Microsoft.VisualBasic.Right(Me.txtFileName.Text, Len(Me.txtFileName.Text) - k).Trim
            Dim sFileName As String = Replace(sFName, ".xlsx", "")


            sCostCenter = GetCostCenter(oDt.Rows(1).Item("F2").ToString)

            If sCostCenter = String.Empty Then
                sErrDesc = "Cost Center is blank in Customer Master :: " & oDt.Rows(1).Item("F2").ToString
                WriteToStatusScreen(False, "ERROR :" & sErrDesc)
                Throw New ArgumentException(sErrDesc)
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARI Object", sFuncName)
            oDoc = p_oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)

            oDoc.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Service

            oDoc.CardCode = oDt.Rows(1).Item("F2").ToString
            oDoc.DocDate = CDate(oDt.Rows(2).Item("F2").ToString)
            oDoc.DocDueDate = CDate(oDt.Rows(2).Item("F2").ToString)
            oDoc.TaxDate = CDate(oDt.Rows(2).Item("F2").ToString)
            oDoc.DocCurrency = Row.Item(16).ToString.Trim
            oDoc.NumAtCard = Row.Item(6).ToString
            oDoc.DocRate = CDbl(Row.Item(21))
            oDoc.UserFields.Fields.Item("U_AI_APARUploadName").Value = sFileName


            'Line 1 Surgeon Fee / Specialist Fee
            If Not IsDBNull(Row.Item(8)) Then
                If Convert.ToDouble(Row.Item(8)) <> 0 Then
                    bLine = True
                    AddDocLine(oDoc, Row, sCostCenter, CDbl(Row.Item(8)), "Surgeon Fee / Specialist Fee")
                End If
            End If

            'Line 2 Anaesthetist
            If Not IsDBNull(Row.Item(9)) Then
                If Convert.ToDouble(Row.Item(9)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, CDbl(Row.Item(9)), "Anaesthetist")
                End If
            End If


            'Line 3 Consultation Fee
            If Not IsDBNull(Row.Item(10)) Then
                If Convert.ToDouble(Row.Item(10)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, CDbl(Row.Item(10)), "Consultation Fee")
                End If
            End If

            'Line 4 Drugs Cost
            If Not IsDBNull(Row.Item(11)) Then

                If CDbl(Row.Item(11)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, CDbl(Row.Item(11)), "Drugs Cost")
                End If
            End If

            'Line 5 Inpatient Services
            If Not IsDBNull(Row.Item(12)) Then

                If CDbl(Row.Item(12)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, CDbl(Row.Item(12)), "Inpatient Services")
                End If
            End If

            'Line 6 Discount
            If Not IsDBNull(Row.Item(13)) Then

                If CDbl(Row.Item(13)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, -1 * CDbl(Row.Item(13)), "Discount")
                End If
            End If

            'Line 7 Deductible / Co-insurance
            If Not IsDBNull(Row.Item(14)) Then

                If CDbl(Row.Item(14)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, -1 * CDbl(Row.Item(14)), "Deductible / Co-insurance")
                End If
            End If

            'Line 8 Non payable Amount
            If Not IsDBNull(Row.Item(15)) Then

                If CDbl(Row.Item(15)) <> 0 Then
                    If bLine = False Then
                        bLine = True
                    Else
                        oDoc.Lines.Add()
                    End If
                    AddDocLine(oDoc, Row, sCostCenter, -1 * CDbl(Row.Item(15)), "Non payable Amount")
                End If
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Invoice.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                p_oCompany.GetLastError(lErrCode, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AR Invoice failed.", sFuncName)
                WriteToStatusScreen(False, "Adding AR Invoice Failed")
                WriteToStatusScreen(False, sErrDesc)
                Throw New ArgumentException(sErrDesc)
            End If

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddARInvoice = RTN_SUCCESS

        Catch ex As Exception
            AddARInvoice = RTN_ERROR
            sErrDesc = ex.Message
            WriteToLogFile(sErrDesc, sFuncName)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Public Function GetCostCenter(ByVal sCardCode As String) As String
        Dim sCostCenter As String = String.Empty
        Dim sSQL As String
        Dim oDS As New DataSet

        sSQL = "SELECT ""U_AI_DefaultCostCent"" FROM OCRD where ""CardCode""='" & sCardCode & "'"
        oDS = ExecuteSQLQuery(sSQL)
        If oDS.Tables(0).Rows.Count > 0 Then sCostCenter = oDS.Tables(0).Rows(0).Item(0).ToString

        Return sCostCenter

    End Function

    Private Sub AddDocLine(ByVal oDoc As SAPbobsCOM.Documents, ByVal Row As DataRow, _
                           ByVal sCostCenter As String, _
                           ByVal dLineTotal As Double, _
                           ByVal sDesc As String)

        'oDoc.Lines.VatGroup = "ZO"
        oDoc.Lines.CostingCode = sCostCenter
        oDoc.Lines.LineTotal = dLineTotal
        oDoc.Lines.ItemDescription = sDesc
        oDoc.Lines.UserFields.Fields.Item("U_AI_PatientName").Value = Row.Item(1).ToString
        oDoc.Lines.UserFields.Fields.Item("U_AI_PolicyNo").Value = Row.Item(2).ToString
        oDoc.Lines.UserFields.Fields.Item("U_AI_ProviderName").Value = Row.Item(3).ToString
        oDoc.Lines.UserFields.Fields.Item("U_AI_ProviderAddress").Value = Row.Item(4).ToString
        oDoc.Lines.UserFields.Fields.Item("U_AI_Admitdate").Value = Row.Item(5)
        oDoc.Lines.AccountCode = "4-32000-00"
    End Sub

    Private Sub frmUpload_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        End
    End Sub


    Private Function AddAPInvoice(ByVal oRows() As DataRow, _
                                  ByRef sErrDesc As String) As Long

        Dim sFuncName As String = String.Empty
        Dim sCardCode As String = String.Empty
        Dim oDoc As SAPbobsCOM.Documents
        Dim lRetCode, lErrCode As Long
        Dim sRemarks As String = String.Empty
        Dim sCostCenter As String = String.Empty
        Dim bLine As Boolean = False
        Dim iCnt As Integer
        Dim oRs As SAPbobsCOM.Recordset

        Try
            sFuncName = "AddAPInvoice"

            'Check for duplicate VendorRef.No

            If IsVendorRefNoExists(oRows(0).Item(5).ToString) = True Then
                sErrDesc = "ERROR::" & "Vendor Ref.No : " & oRows(0).Item(5).ToString & "already exists in the system"
                WriteToStatusScreen(False, sErrDesc)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Vendor ref.No already exists. Adding API failed.", sFuncName)
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile(sErrDesc, sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            Dim k As Integer = Microsoft.VisualBasic.InStrRev(Me.txtFileName.Text, "\")
            Dim sFName As String = Microsoft.VisualBasic.Right(Me.txtFileName.Text, Len(Me.txtFileName.Text) - k).Trim
            Dim sFileName As String = Replace(sFName, ".xlsx", "")


            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Creating DIAPI ARI Object", sFuncName)
            oDoc = p_oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oPurchaseInvoices)
            oRs = p_oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

            oDoc.DocType = SAPbobsCOM.BoDocumentTypes.dDocument_Service

            oDoc.CardCode = oRows(0).Item(0).ToString
            oDoc.DocDate = oRows(0).Item(2).ToString
            oDoc.DocDueDate = oRows(0).Item(3).ToString
            oDoc.TaxDate = oRows(0).Item(4).ToString
            oDoc.NumAtCard = oRows(0).Item(5).ToString
            oDoc.DocCurrency = oRows(0).Item(11).ToString
            oDoc.UserFields.Fields.Item("U_AI_APARUploadName").Value = sFileName

            iCnt = 0

            For Each LineRow As DataRow In oRows
                iCnt += 1
                If iCnt > 1 Then
                    oDoc.Lines.Add()
                End If

                oDoc.Lines.AccountCode = LineRow(8).ToString
                oDoc.Lines.ItemDescription = LineRow(7).ToString
                oDoc.Lines.LineTotal = CDbl(LineRow(12))
                oDoc.Lines.VatGroup = LineRow(10).ToString

                sCostCenter = LineRow(9).ToString
                Dim sSQL As String = "SELECT T0.""PrcCode"" FROM OPRC T0 WHERE T0.""PrcCode""='" & sCostCenter & "' and T0.""DimCode"" =1"
                oRs.DoQuery(sSQL)
                If Not oRs.EoF Then
                    oDoc.Lines.CostingCode = sCostCenter
                Else
                    oDoc.Lines.CostingCode2 = sCostCenter
                End If

                'oDoc.Lines.CostingCode = LineRow(9).ToString
                
            Next

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding AP Invoice.", sFuncName)
            lRetCode = oDoc.Add
            If lRetCode <> 0 Then
                p_oCompany.GetLastError(lErrCode, sErrDesc)
                WriteToStatusScreen(False, "Failed to create AP Invoice .....")
                If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Adding API failed.", sFuncName)
                Throw New ArgumentException(sErrDesc)
            End If

            WriteToStatusScreen(False, "Successfully created AP Invoice .....")

            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile_Debug("Fucntion Completed Succesfully.", sFuncName)
            AddAPInvoice = RTN_SUCCESS

        Catch ex As Exception
            AddAPInvoice = RTN_ERROR
            sErrDesc = ex.Message
            WriteToLogFile(sErrDesc, sFuncName)
            If p_iDebugMode = DEBUG_ON Then Call WriteToLogFile("Function completed with ERROR:" & sErrDesc, sFuncName)
        End Try
    End Function

    Public Function IsVendorRefNoExists(ByVal sVendorRef As String) As Boolean
        Dim bIsExists As Boolean = False
        Dim sSQL As String
        Dim oDS As New DataSet

        sSQL = " SELECT T0.""NumAtCard"" FROM OPCH T0" & _
               " WHERE T0.""NumAtCard"" = '" & sVendorRef & "'"

        oDS = ExecuteSQLQuery(sSQL)

        If oDS.Tables(0).Rows.Count > 0 Then bIsExists = True
        Return bIsExists

    End Function

End Class
